<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Mobile_model extends CI_Model {

    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
        
    }

    function get_item($itemID)
    {
		$this->db->select('item_name');
		$this->db->from('t_mtr_item');
		$this->db->where('item_id', $itemID);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->item_name;
		}
		else
			return NULL;
    }
	
	
	function get_itemdetail($iccid)
    {
		$this->db->select('item_id,item_name,iccid,default_price,item_group_id');
		$this->db->from('t_mtr_item');
		$this->db->where('iccid', $iccid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
    }
	
	function get_itemsellin($iccid)
    {
		$this->db->select('iccid');
		$this->db->from('t_trx_sell_in_detail');
		$this->db->where('iccid', $iccid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
    }

	function cekhandset($userid,$imsi,$imei)
    {
		$this->db->select('imei,imsi');
		$this->db->from('t_mtr_handset');
		$this->db->where("user_id = '".$userid."' and imsi = '".$imsi."' and imei = '".$imei."'");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
    }

	function get_itemsfromarray($matchs,$cluster)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}

		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("t_mtr_item.iccid,t_mtr_item.item_id,t_mtr_item.item_name,t_mtr_item.default_price,t_mtr_item.item_group_id");
		$this->db->from("t_mtr_item","t_mtr_distributor");
		$this->db->join("t_mtr_distributor","t_mtr_distributor.distributor_id = t_mtr_item.distributor_id");
		$this->db->where("t_mtr_item.iccid = ANY(ARRAY[$cocokan]) and t_mtr_distributor.territory_id = '".$cluster."' and t_mtr_item.istatus = 1");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;
		
		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$detailitem[$hasilperbaris['iccid']] = array("item_id" => $hasilperbaris['item_id'], "item_name" => $hasilperbaris['item_name'], "item_code" => $hasilperbaris['iccid'], "price" => $hasilperbaris['default_price'], "item_group_id" => $hasilperbaris['item_group_id']);
			$intcek++;
		}
		
		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value'], "detail" => $detailitem[$dataitem['value']]);
				}
				else
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}

	function get_iccidsellscaninfromarray($matchs)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}

		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("iccid");
		$this->db->from("t_trx_scan_in_detail");
		$this->db->where("iccid = ANY(ARRAY[$cocokan])");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;

		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$intcek++;
		}

		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);

				}
				else
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}

	function get_iccidscaninfromarray($matchs)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}

		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("iccid");
		$this->db->from("t_trx_scan_in_detail");
		$this->db->where("iccid = ANY(ARRAY[$cocokan])");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;
		
		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$intcek++;
		}

		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);

				}
				else
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value'], "detail" => $dataitem['detail']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}

	function get_iccidsellinfromarray($matchs)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}

		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("iccid");
		$this->db->from("t_trx_sell_in_detail");
		$this->db->where("iccid = ANY(ARRAY[$cocokan])");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;
		
		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$intcek++;
		}

		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);

				}
				else
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}
	
	function get_iccidsellreturnfromarray($matchs)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}

		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("iccid");
		$this->db->from("t_trx_sell_return_detail");
		$this->db->where("iccid = ANY(ARRAY[$cocokan])");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;
		
		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$intcek++;
		}

		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);

				}
				else
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}

	function get_iccidstockscanfromarray($matchs)
	{
		$intcek = 0;
		$cocokan = "";
		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				continue;

			if($intcek == 0)
				$cocokan .= "'".$dataitem['value']."'";
			else
				$cocokan .= ",'".$dataitem['value']."'";
			$intcek++;
		}
		
		if($intcek == 0)
			$cocokan = "''";

		$this->db->select("iccid");
		$this->db->from("t_trx_stock_scan_detail");
		$this->db->where("iccid = ANY(ARRAY[$cocokan])");
		$query = $this->db->get();

		$itemada = array();
		$intcek = 0;
		
		if($query->num_rows() > 0)
		foreach($query->result_array() as $hasilperbaris)
		{
			$itemada[$intcek] = $hasilperbaris['iccid'];
			$intcek++;
		}

		$hasilnya = array();
		$intcek = 0;

		foreach($matchs as $dataitem)
		{
			if($dataitem['true'] == "invalid")
				$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
			else
			{
				if(in_array($dataitem['value'],$itemada))
				{
					$hasilnya[$intcek] = array("true" => "invalid", "value" => $dataitem['value']);

				}
				else
				{
					$hasilnya[$intcek] = array("true" => $dataitem['true'], "value" => $dataitem['value']);
				}
			}
			$intcek++;
		}

		return $hasilnya;
	}

	function get_prodcatalog()
	{
		$this->db->select("item_id, item_name, item_code, iccid, istatus, default_price");
		$this->db->from("t_mtr_item");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	

	function get_prodcatalog2($itemgroupid)
	{
		$this->db->select("item_id, item_name, item_code, iccid, istatus, default_price");
		$this->db->from("t_mtr_item");
		$this->db->where("item_group_id",$itemgroupid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_itemgroup()
	{
		$this->db->select("item_group_id, item_group_name");
		$this->db->from("t_mtr_item_group");
		$query = $this->db->get();
		//$query = $this->db->query("SELECT grp.item_group_id, grp.item_group_name FROM t_mtr_item itm JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id");
		
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function add_scanin($data,$jumlah,$client)
    {
		$userid = $data['user_id'];
		$data = array(
			'user_id' => $userid,
			'scan_date' =>  date("Y-m-d H:i:s"),
			//'quantity' => $jumlah,
			//'real_qty' => $jumlah,
			'created_by' => $userid,
			'updated_by' => $userid,
			'istatus' => 1,
			'acess_via' => $client
		);
		$this->db->insert('t_trx_scan_in', $data);
		return $this->db->insert_id();
    }
    
	function add_absent($lon,$lat,$acc,$uid)
    {

		$data = array(
			'user_id' => $uid,
			'lon' =>  $lon,
			'lat' => $lat,
			'acc' => $acc,
			'created_by' => $uid
		);
		$this->db->insert('t_trx_absent', $data);
		return $this->db->insert_id();
    }



	function add_scanin_detail($ICCID,$scanid)
	{
        if($this->db->query("select count(iccid) as jumlah from t_trx_scan_in_detail where iccid='".$ICCID."' and istatus=1")->row()->jumlah==0){
    
		$data = array(
			'scan_in_id' => $scanid,
			'iccid' => $ICCID,
			'istatus' => 1
		);
		$this->db->insert('t_trx_scan_in_detail', $data);
        }
	}

	/**
	 * The main idea behind ValidateItem is to compromise the item status,
	 * whether it is available in the cluster office, or have become other SFA's stock, have been sold or consigned.
	 * Wherever the item is or whatever the transaction type is, make sure it has not been sold !!!
	 * @return  Array()  Periksa tabel SellIn dan juga Consignment, setelah digabungkan cari unique valuenya.
	 *                   Setelah dibandingkan dengan $iccid, maka didapatlah ICCID yang valid. 
	 *                   Dalam artian, $iccid tersebut belum ada di SellIn dan juga Consignment
	 */
	public function ValidateItem($iccid)
	{
		$this->db->select('iccid');
		$this->db->where_in('iccid', $iccid);
		$query = $this->db->get('t_trx_sell_in_detail');
		$SellIn = array();
		foreach ($query->result_array() as $key => $val) 
			{ $SellIn[] = $val['iccid']; }

		$this->db->select('iccid');
		$this->db->where_in('istatus', array(1,3) ); // status 1: konsinyasi baru, 3: konsinyasi terjual
		$this->db->where_in('iccid', $iccid);
		$query = $this->db->get('t_trx_consignment_detail');
		$Consigned = array();
		foreach ($query->result_array() as $key => $val) 
			{ $Consigned[] = $val['iccid']; }

    // Use brain, don't google
    $CombinedICCID   = array_unique( array_merge($SellIn, $Consigned) );
    $valid_iccid     = array_diff($iccid, $CombinedICCID);
    $invalid_iccid   = array_intersect($iccid, $CombinedICCID);
    $data = array('valid' => $valid_iccid, 'invalid' => $invalid_iccid);
    return $data;
	}

	function add_sellin($data) {
		$datas = array(
			'user_id'      => $data['user_id'],
			'channel_id'   => $data['channel_id'],
			'top_id'       => $data['top_id'],
			'created_by'   => $data['user_id'],
			'created_on'   => date('Y-m-d H:i:s'),
			'sell_in_date' => date('Y-m-d'),
			'istatus'      => 1,
			'acess_via'    => 'mobile-android'
		);
		$this->db->insert('t_trx_sell_in', $datas);
		return $this->db->insert_id();
	}


	public function add_sellin_detail($iccid, $trx_id, $sales_id)
	{
		foreach ($iccid as $key) {
			$data[] = array(
					'sell_in_id' => $trx_id,
					'iccid'      => $key,
					'istatus'    => 1,
					'created_by' => $sales_id
				);
		}
		$this->db->insert_batch('t_trx_sell_in_detail', $data);
	}

	
	function add_sellout($data,$terrid)
    {
		$userid = $data['user_id'];
		$channelid = $terrid;
		
			
		if($data['date'] == "")
		{
			$tanggal = date("Y-m-d H:i:s");
		}
		else
			$tanggal = $data['date'];
			
		$tanggalaja = explode(" ",$tanggal);

		$data = array(
			'sales_id' => $userid,
			'territory_id' => $channelid,
			'created_by' => $userid,
			'created_on' => $tanggal,
			'sellout_date' => $tanggalaja[0],
			'updated_by' => $userid,
            'access_via' => 'mobile'
			
		);
		$this->db->insert('t_trx_sellout', $data);
		return $this->db->insert_id();
    }

	function add_sellout_detail($ICCID,$sellinid,$data)
	{
	   if($this->db->query("select count(iccid) as jumlah from t_trx_sellout_detail where iccid='".$ICCID."'")->row()->jumlah==0)
       {
    		$data = array(
                'created_by' =>$data['user_id'],
                'updated_by' =>$data['user_id'],
    			'sellout_id' => $sellinid,
    			'iccid' => $ICCID
    		);
    		$this->db->insert('t_trx_sellout_detail', $data);
        }
	}

	function add_claim($data,$terrid,$client)
    {
		$userid = $data['user_id'];
		//$channelid = $data['channel_id'];
		//$sell_event_id = $data['sell_event_id'];

		
			$tanggal = date("Y-m-d H:i:s");
		

		$data1 = array(
			'user_id' => $userid,
			'territory_id' => $terrid,
			'created_by' => $userid,
			'created_on' => $tanggal,
			'updated_by' => $userid,
			'claim_date' => $tanggal,
			'requestor' => $data['frontliner_code'],
			'program' => $data['claim_program'],
			//'sell_event_id' => 1,
			'istatus' => 1,
			'acess_via' => $client
		);
        
        

     
		$this->db->insert('t_trx_card_claim', $data1);

		return $this->db->insert_id();
      
    }
	
	function add_claim_detail($ICCID,$claimid)
	{
	   
		$data = array(
			'claim_id' => $claimid,
			'iccid' => $ICCID,
			'istatus' => 1
		);
		$this->db->insert('t_trx_card_claim_detail', $data);
	}
	
	public function add_konsinyasi($data)
	{
		$territory_id = $this->get_territory($data['channel_id']);
		if($data['term_id'] == "") 
			{ $data['term_id'] = 1; }
		$trx = array(
				'user_id'          => $data["user_id"],
				'territory_id'     => $territory_id,
				'consignment_date' => date('Y-m-d'),
				'term_id'          => $data["term_id"],
				'istatus'          => 1,
				'created_on'       => date('Y-m-d H:i:s'),
				'created_by'       => $data["user_id"],
				'acess_via'        => 'mobile-android'
			);
		$this->db->insert('t_trx_consignment', $trx);
		return $this->db->insert_id();
	}

	// function add_konsinyasi($data, $terrid, $client)
 //    {
	// 	$userid = $data['user_id'];
	// 	$channelid = $data['channel_id'];
	// 	//$sell_event_id = $data['sell_event_id'];

		
	// 		$top_id = $data['term_id'];
	// 	if($top_id=="")
	// 		$top_id='1';
			
	// 	if($data['date'] == "")
	// 	{
	// 		$tanggal = date("Y-m-d H:i:s");
	// 	}
	// 	else
	// 		$tanggal = $data['date'];
		

	// 	$data = array(
	// 		'user_id' => $userid,
	// 		'territory_id' => $terrid,
	// 		'created_by' => $userid,
	// 		'created_on' => $tanggal,
	// 		'updated_by' => $userid,
	// 		'consignment_date' => $tanggal,
	// 		'term_id' => $top_id,
	// 		'istatus' => 1,
	// 		'acess_via' => $client
	// 	);
	// 	$this->db->insert('t_trx_consignment', $data);
	// 	return $this->db->insert_id();
 //    }
	
	function add_konsinyasi_detail($iccid, $trx_id, $userid)
	{
		$data = array(
				'consignment_id' => $trx_id,
				'iccid'      => $iccid,
				'istatus'    => 1,
				'created_by' => $userid
			);

		$this->db->insert('t_trx_consignment_detail', $data);
		$data2 = array(
			'istatus' => 5,
			'transaction_type' => 'Konsinyasi',
			'transaction_id' => $trx_id
		);
		$this->db->update('t_trx_scan_in_detail', $data2, array('iccid' => $iccid));
	}
	
	function add_konsinyasi_program($trx_id, $programid, $userid)
	{
		$data = array(
			'consignment_id' => $trx_id,
			'program_id' => $programid,
			'created_by' => $userid,
			'created_on' => date("Y-m-d h:i:s")
		);
		$this->db->insert('t_trx_consignment_program', $data);
	}
	
	function update_scanin($iccid,$istatus,$trxid,$trxtype)
    {
		$data = array(
			'istatus' => $istatus,
            'transaction_type'=>$trxtype,
            'transaction_id'=>$trxid
		);
		$this->db->where("iccid = '".$iccid."'");
		return $this->db->update('t_trx_scan_in_detail', $data);
    }
	
	function update_item($iccid,$istatus)
    {
		$data = array(
			'istatus' => $istatus
		);
		$this->db->where("iccid = '".$iccid."'");
		return $this->db->update('t_mtr_item', $data);
    }

	function update_journey($journeyid,$userid,$check_in_date)
    {
		if($check_in_date == "")
			$tanggal =  date("Y-m-d H:i:s");
		else
			$tanggal = $check_in_date;

		$data = array(
			'check_in_date' => $tanggal
		);
		$this->db->where("journey_id = '".$journeyid."' and user_id = '".$userid."'");
		return $this->db->update('t_trx_journey', $data);
    }

	/** Old Function
 * function add_journey($userid,$check_in_date,$route_date,$channelid,$jcid)
 *     {
 * 		if($check_in_date == "")
 * 			$tanggal = date("Y-m-d h:i:s");
 * 		else
 * 			$tanggal = $check_in_date;

 * 		$data = array(
 * 			'user_id' => $userid,
 * 			'jc_id' => $jcid,
 * 			'check_in_date' => $tanggal,
 * 			'channel_id' => $channelid,
 * 			'route_date' => $route_date,
 * 			'created_by' => $userid,
 * 			'created_on' => date("Y-m-d h:i:s"),
 * 			'updated_by' => $userid,
 * 			'updated_on' => date("Y-m-d h:i:s")
 * 		);
 * 		return $this->db->insert('t_trx_journey', $data);
 *     }
 */
 function add_journey($userid,$check_in_date,$route_date,$channelid,$jcid,$lat=null,$lon=null,$lac=null,$type,$parring_id,$via,$remark)
    {
        //Patched By RAA 
        //Insert Check In to Tracking too
		if($check_in_date == "")
			$tanggal = date("Y-m-d h:i:s");
		else
			$tanggal = $check_in_date;

		$data = array(
			'user_id' => $userid,
			'jc_id' => $jcid,
			'check_in_date' => $tanggal,
			'channel_id' => $channelid,
			'route_date' => $route_date,
			'created_by' => $userid,
			'created_on' => date("Y-m-d h:i:s"),
			'updated_by' => $userid,
			'updated_on' => date("Y-m-d h:i:s"),
            'type' => $type,
            'parring_id'=>$parring_id,
            'via'=>$via,
            'remark'=>$remark
		);
        $this->db->insert('t_trx_journey', $data);
        $journey_id=$this->db->insert_id();
        if(($lon == "" || $lon === NULL) && ($lat == "" || $lat === NULL))
			$dataTrack = array(
				'user_id' => $userid,
				'track_date' =>  date("Y-m-d H:i:s"),
				'lon' => NULL,
				'lat' => NULL,
				'created_by' => $userid,
				'journey_id' => $journey_id,
                'accuracy' =>$lac
			);
		else
			$dataTrack = array(
				'user_id' => $userid,
				'track_date' =>  date("Y-m-d H:i:s"),
				'lon' => $lon,
				'lat' => $lat,
				'created_by' => $userid,
				'journey_id' => $journey_id,
                'accuracy' =>$lac
			);
        $this->db->insert('t_trx_track', $dataTrack);
		return;
        //end of patch
    }


	function add_sellreturn($data,$client)
    {
		$userid = $data['user_id'];
		$channelid = $data['channel_id'];
		
		if($data['date'] == "")
		{
			$tanggal = date("Y-m-d H:i:s");
		}
		else
			$tanggal = $data['date'];

		$data = array(
			'user_id' => $userid,
			'channel_id' => $channelid,
			'created_by' => $userid,
			'created_on' => $tanggal,
			'updated_by' => $userid,
			'updated_on' => $tanggal,
			'istatus' => 1,
			'acess_via' => $client
		);
		$this->db->insert('t_trx_sell_return', $data);
		return $this->db->insert_id();
    }

	function add_sellreturn_detail($ICCID,$sellreturnid)
	{
		$data = array(
			'return_id' => $sellreturnid,
			'iccid' => $ICCID,
			'istatus' => 1
		);
		$this->db->insert('t_trx_sell_return_detail', $data);
	}

	function add_stockscan($data,$jumlah)
    {
		$userid = $data['user_id'];
		$channelid = $data['channel_id'];
		
		if($data['date'] == "")
		{
			$tanggal = date("Y-m-d H:i:s");
		}
		else
			$tanggal = $data['date'];

		$data = array(
			'channel_id' => $channelid,
			'created_by' => $userid,
			'created_on' => $tanggal,
			'updated_by' => $userid,
			'updated_on' => $tanggal,
			'quantity' => $jumlah
		);
		$this->db->insert('t_trx_stock_scan', $data);
		return $this->db->insert_id();
    }

	function add_stockscan_detail($ICCID,$stockscanid)
	{
		$data = array(
			'stock_scan_id' => $stockscanid,
			'iccid' => $ICCID
		);
		$this->db->insert('t_trx_stock_scan_detail', $data);
	}

	function add_trackuser($lon,$lat,$userid,$cellid,$lac)
	{
		if(($lon == "" || $lon === NULL) && ($lat == "" || $lat === NULL))
			$data = array(
				'user_id' => $userid,
				'track_date' =>  date("Y-m-d H:i:s"),
				'lon' => "",
				'lat' => "",
				'created_by' => $userid,
				'cell_id' => $cellid,
                'accuracy' =>$lac
			);
		else
			$data = array(
				'user_id' => $userid,
				'track_date' =>  date("Y-m-d H:i:s"),
				'lon' => $lon,
				'lat' => $lat,
				'created_by' => $userid,
				'cell_id' => $cellid,
                'accuracy' =>$lac
			);
		$this->db->insert('t_trx_track', $data);
	}

	function get_user($data)
	{
		$this->db->select("user_id, user_group_id, is_allowed_time,logon_attempt,user_password,must_change_password");
		$this->db->from("t_mtr_user");
		$this->db->where("user_id = '".$data['username']."' and istatus = 1");// user_password = md5('".$data['password']."')");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_user_by_id($usrid)
	{
		$this->db->select("user_id, user_group_id, is_allowed_time,logon_attempt,user_password,must_change_password");
		$this->db->from("t_mtr_user");
		$this->db->where("user_id = '".$usrid."' and istatus = 1");// user_password = md5('".$data['password']."')");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function update_user($userid,$attempt)
	{
		$data = array(
			'logon_attempt' => $attempt
		);
		$this->db->where("user_id",$userid);
		$this->db->update('t_mtr_user', $data);
	}
	
	function update_user_must_change_password($userid)
	{
		$data = array(
			'must_change_password' => 0
		);
		$this->db->where("user_id",$userid);
		$this->db->update('t_mtr_user', $data);
	}
	
	function update_user_password($userid,$pass)
	{
		$data = array(
			'user_password' => md5($pass)
		);
		$this->db->where("user_id",$userid);
		$this->db->update('t_mtr_user', $data);
	}


	function get_servicetarget($data)
	{
		$this->db->select("services_id");
		$this->db->from("t_trx_service_target");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_servicedetail($groupid)
	{
		//$this->db->select("services_name, services_tabel, services_id");
		//$this->db->from("t_trx_service");
		$query = $this->db->query("SELECT a.services_name, a.services_tabel,a.services_id FROM t_trx_service a,t_trx_service_authority b WHERE a.services_id=b.service_id AND b.group_id=$groupid");
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_fieldname($namatabel)
	{
		$query = $this->db->query("SELECT a.attname as Column, pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = (SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^($namatabel)$' AND pg_catalog.pg_table_is_visible(c.oid))");
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function add_formdinamik($query)
	{
		$this->db->query($query);
	}

	function get_fieldnamefromproperty($serviceid)
	{
		$this->db->select("t_services_column,t_elm_type,t_elm_label,t_source_name,t_source_column_display,t_source_column_value");
		$this->db->from("t_trx_service_property");
		$this->db->where("services_id = $serviceid and enabled_mobile=1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_datasourcelink($namatabel,$kolom1,$kolom2)
	{
		if($namatabel != "" && $kolom1 != "" && $kolom2 != "")
		{
			$this->db->select($kolom1." as jjj0,".$kolom2." as jjj1");
			$this->db->from($namatabel);
			$query = $this->db->get();
			if ($query->num_rows() > 0)
			{
				return $query->result_array();
			}
			else
				return NULL;
		}
		else
			return NULL;
	}

	function get_availtjc($userid)
	{
		$this->db->select("count(1)");
		$this->db->from("pg_tables");
		$this->db->where("tablename = 't_jc_".$userid."' and schemaname = 'public'");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_journey($userid,$jcid)
	{
		$this->db->select("*");
		$this->db->from("t_jc_".$userid);
		$this->db->where("jc_id",$jcid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_journeyagenttoday($userid)
	{
		if(date("N") == 1)
			$hds = 7;
		else
			$hds = date("N") - 1;

		$this->db->select("jc_id, channel_id, '".date("Y-m-d")."' as tglku", FALSE);
		$this->db->from("t_jc_".$userid);
		$this->db->where("yyyymm = '".date("YW")."' and d".$hds." = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_journeyoutlettoday($userid)
	{
		$this->db->select("jc_id, channel_id, '".date("Y-m-d")."' as tglku", FALSE);
		$this->db->from("t_jc_".$userid);
		$this->db->where("yyyymm = '".date("Ym")."' and d".date("j")." = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_journeytoday_by_group($groupid,$userid)
	{
		
        try{
            if ($groupid=="8" || $groupid=="9" ){
                
            $this->db->select("jc_id, t_jc_".$groupid.".territory_id, '".date("Y-m-d")."' as tglku", FALSE);
    		$this->db->from("t_jc_".$groupid);
            $this->db->join("t_mtr_territory","t_mtr_territory.territory_id = t_jc_".$groupid.".territory_id");
    		$this->db->join("t_mtr_agent","t_mtr_agent.territory_id=t_jc_".$groupid.".territory_id","left");
            $this->db->where("yyyymm = '".date("Ym")."' and t_jc_".$groupid.".user_id = '$userid' and d".date("j")." = 1 and (t_mtr_territory.user_id='$userid' or (t_mtr_territory.parent_id=f_get_cluster('$userid') and t_mtr_agent.agent_selling_type=2))");
    		$query = $this->db->get();
            
            }else{
            if($this->db->table_exists("t_jc_".$groupid)){
            $this->db->select("jc_id, t_jc_".$groupid.".territory_id, '".date("Y-m-d")."' as tglku", FALSE);
    		$this->db->from("t_jc_".$groupid);
            $this->db->where("yyyymm = '".date("Ym")."' and t_jc_".$groupid.".user_id = '$userid' and d".date("j")." = 1");
    		$query = $this->db->get();
            }else{
            return null;
            }
            //echo $groupid.'\n';
    		}
        }catch(exception $e){
            return null;
        }
        
        
        
        if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return null;
	}

	function get_agent($agentid)
	{
		$this->db->select("*");
		$this->db->from("t_mtr_agent");
		$this->db->where("agent_id = '".$agentid."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function add_eload($userid,$outletid,$eload_number,$eload_value,$date)
    {
	if($date == "")
		{
			$tanggal = date("Y-m-d H:i:s");
		}
		else
			$tanggal = $date;
		
        $tglaja=explode(" ",$tanggal);
		$data = array(
			'channel_id' => $outletid,
			'eload_number' => $eload_number,
			'amount' => $eload_value,
			'created_by' => $userid,
			'created_on' => $tanggal,
			'updated_by' => $userid,
			'updated_on' => $tanggal,
            'sales_id'=>$userid,
            'sell_date'=>$tglaja[0],
            'access_via'=>'mobile-android'
		);
		$this->db->insert('t_trx_eload', $data);

    }
	
	function get_eloadnumber($outletid)
	{
		$this->db->select("eload_number");
		$this->db->from("t_mtr_outlet_eload");
		$this->db->where("outlet_id = '".$outletid."'");
        $this->db->limit('1');
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_agent_eloadnumber($outletid)
	{
		$this->db->select("agent_mdn_evo");
		$this->db->from("t_mtr_agent");
		$this->db->where("agent_id = '".$outletid."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_outlet($outletid)
	{
		$this->db->select("*, f_get_is_sgo('$outletid') as sgo");
		$this->db->from("t_mtr_outlet");
		$this->db->where("outlet_id = '".$outletid."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_outlet_frontliner($outletid)
	{
		$this->db->select("outlet_frontliner_id , frontliner_name, frontliner_identity_no, frontliner_code");
		$this->db->from("t_mtr_outlet_frontliner");
		$this->db->where("outlet_id = '".$outletid."'");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_outletbyterritoryid($territoryid)
	{
		$this->db->select("mo.outlet_id, mo.lon, mo.lat, mo.outlet_name, mo.address, mo.city,moe.eload_number");
		$this->db->from("t_mtr_outlet mo");
        $this->db->join("t_mtr_outlet_eload moe","moe.outlet_id=mo.outlet_id","left");
		//$this->db->where("territory_id=$territoryid and istatus = 1");
		$this->db->where("mo.territory_id = '".$territoryid."' and mo.istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_agentbyterritoryid($territoryid)
	{
		$this->db->select("agent_id, lon, lat, agent_name, agent_address, agent_city, agent_business_focus, agent_type,agent_selling_type, agent_mdn_evo");
		$this->db->from("t_mtr_agent");
		//$this->db->where("territory_id",$territoryid);
		$this->db->where("territory_id = '".$territoryid."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_territorybyuserid($userid,$tipeterritory)
	{
		$this->db->select("territory_id");
		$this->db->from("t_mtr_territory");
		$this->db->where("user_id = '".$userid."' and territory_type_id = ".$tipeterritory);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function add_territorymtr($name,$territoryid,$userid,$tipeterritory)
	{
		$data = array(
			'territory_name' => $name,
			'parent_id' => $territoryid,
			'user_id' => $userid,
			'territory_type_id' => $tipeterritory,
			'created_by' => $userid,
			'updated_by' => $userid,
			'istatus' => 0
		);
		$this->db->insert('t_mtr_territory', $data);
		return $this->db->insert_id();
	}

	function add_territorymtruser($userid,$territoryid)
	{
		$data = array(
			'user_id' => $userid,
			'territory_id' => $territoryid,
			'created_by' => $userid,
			'updated_by' => $userid
		);
		$this->db->insert('t_mtr_user_territory', $data);
	}

	function add_outlet($userid,$territoryidlast,$outletname,$outletaddress,$outletcity,$longitude,$latitude,$imagepath,$outletbf,$eloadnumber)
	{
		$query = $this->db->query("SELECT sp_mobile_outlet('#','".$userid."','".$outletname."',$territoryidlast,'".$outletaddress."','".$outletcity."','".$outletbf."','".$eloadnumber."','".$longitude."','".$latitude."','".$imagepath."')");
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->sp_mobile_outlet;
		}
		else
			return NULL;
		//$query = $this->db->get();
	
			
		//$query = $this->db->get();
		
		//$idoutlets = $query->result_array();
	//	return $idoutlets[0]['sp_mobile_outlet'];

		/*
		$data = array(
			'outlet_id' => "#",
			'territory_id' => $territoryidlast,
			'outlet_name' => $outletname,
			'address' => $outletaddress,
			'city' => $outletcity,
			'lon' => $longitude,
			'lat' => $latitude,
			'image_path' => $imagepath,
			'created_by' => $userid,
			'updated_by' => $userid,
			'istatus' => 0
		);
		$this->db->insert('t_mtr_outlet', $data);
		return $this->db->insert_id();
		*/
	}
	
	function add_channel_program($channelid,$programid,$userid)
	{
		$data = array(
			'channel_id' => $channelid,
			'program_id' => $programid,
			'created_by' => $userid,
			'created_on' => date("Y-m-d h:i:s"),
			'updated_by' => $userid,
			'updated_on' => date("Y-m-d h:i:s")
		);
		$this->db->insert('t_mtr_channel_program', $data);
	}
	
	function add_sellin_program($sellinid, $programid ,$userid)
	{
		$data = array(
			'sell_in_id' => $sellinid,
			'program_id' => $programid,
			'created_by' => $userid,
			'created_on' => date("Y-m-d h:i:s"),
			'updated_by' => $userid,
			'updated_on' => date("Y-m-d h:i:s")
		);
		$this->db->insert('t_trx_sell_in_program', $data);
	}
	
	function delete_channel_program($channelid)
	{
		$data = array(
			'channel_id' => $channelid
		);
		$this->db->delete('t_mtr_channel_program', $data);
	}
	
	function get_channel_program($channelid)
	{
		$this->db->select("program_id");
		$this->db->from("t_mtr_channel_program");
		$this->db->where("channel_id",$channelid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_program($programid)
	{
		$this->db->select("territory_id,program_type_id");
		$this->db->from("t_mtr_program");
		$this->db->where("program_id",$programid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function add_outletdata($outletid,$outletbf)
	{
		$data = array(
			'outlet_id' => $outletid,
			'outlet_business_focus' => $outletbf
		);
		$this->db->insert('t_mtr_outlet_data', $data);
	}

	function add_agent($userid,$territoryidlast,$agentname,$agentaddress,$agentcity,$agentbf,$agentstype,$longitude,$latitude,$eload,$image,$agenttype)
	{
		/*$data = array(
			'agent_id' => $agentid,
			'territory_id' => $territoryidlast,
			'agent_name' => $agentname,
			'agent_address' => $agentaddress,
			'agent_city' => $agentcity,
			'agent_business_focus' => $agentbf,
			'agent_type' => $agenttype,
			'created_by' => $userid,
			'updated_by' => $userid,
			'lon' => $longitude,
			'lat' => $latitude,
			'agent_mdn_evo' => $eload,
			'image_path' => $image,
			'istatus' => 0
		);
		$this->db->insert('t_mtr_agent', $data);*/
		$query = $this->db->query("SELECT sp_mobile_agent('#','".$userid."','".$agentname."',$territoryidlast,'".$agentstype."','".$agentaddress."','".$agentcity."','".$agentbf."','".$eload."','".$longitude."','".$latitude."','".$image."','".$agenttype."')");
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->sp_mobile_agent;
		}
		else
			return NULL;
	}

	function update_agent($agentid,$territoryidlast,$userid,$agentname,$agentaddress,$agentcity,$agentbf,$agentstype,$eloadnumber,$agenttype)
	{
		/*$data = array(
			'agent_name' => $agentname,
			'agent_address' => $agentaddress,
			'agent_city' => $agentcity,
			'agent_business_focus' => $agentbf,
			'agent_mdn_evo' => $eloadnumber,
			'agent_type' => $agenttype
		);
		$this->db->where("agent_id",$agentid);
		$this->db->update('t_mtr_agent', $data);*/
		
		$query = $this->db->query("SELECT sp_mobile_agent('".$agentid."','".$userid."','".$agentname."',$territoryidlast,'".$agentstype."','".$agentaddress."','".$agentcity."','".$agentbf."','".$eloadnumber."','','','','".$agenttype."')");
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->sp_mobile_agent;
		}
		else
			return NULL;
	}

	function update_outlet($outletid,$territoryidlast,$userid,$outletname,$outletaddress,$outletcity,$outletbf,$eloadnumber)
	{
		/*$data = array(
			'outlet_name' => $outletname,
			'address' => $outletaddress,
			'city' => $outletcity,
			'eload_number' => $eloadnumber
		);
		$this->db->where("outlet_id",$outletid);
		$this->db->update('t_mtr_outlet', $data);*/
        //echo "SELECT sp_mobile_outlet('".$outletid."','".$userid."','".$outletname."',$territoryidlast,'".$outletaddress."','".$outletcity."','".$outletbf."','".$eloadnumber."','','','')";
        //exit();
    $data = array(
    	"outlet_id" => $outletid,
    	"cluster_id" => $territoryidlast,
    	"maintained_by" => $userid,
    	"outlet_name" => $outletname,
    	"outlet_address" => $outletaddress,
    	"outlet_city" => $outletcity,
    	"bussiness_focus" => $outletbf,
    	"eload_number" => $eloadnumber
    );

		$query = $this->db->query("SELECT sp_mobile_outlet('".$outletid."','".$userid."','".$outletname."',$territoryidlast,'".$outletaddress."','".$outletcity."','".$outletbf."','".$eloadnumber."','','','')");
    $this->mglobal->saveLog('Update Data Outlet', 'Data='. json_encode($data) .'mobile-android', $outletid );
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->sp_mobile_outlet;
		}
		else
			return null;
	}

	function update_outletdata($outletid,$outletbf)
	{
		$data = array(
			'outlet_business_focus' => $outletbf
		);
		$this->db->where("outlet_id",$outletid);
		$this->db->update('t_mtr_outlet_data', $data);
	}

	function get_kpi($userid)
	{
		$this->db->select("*");
		$this->db->from("f_get_kpi_by_userid('".$userid."')");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function check_valid_item($iccid,$userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_check_valid_item('$iccid#mobile','$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_check_valid_item;
		}
		else
			return NULL;
	}
	
	function check_valid_item_in_store($iccid,$channelid,$userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_check_valid_item_in_store('$iccid','$channelid','$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_check_valid_item_in_store;
		}
		else
			return NULL;
	}
	
	function get_price($iccid,$userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_price('$iccid','$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_price;
		}
		else
			return NULL;
	}
	
	function get_territory($channelid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_territory_id_by_channel_id('$channelid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_territory_id_by_channel_id;
		}
		else
			return NULL;
	}
	
	function get_itemgroupbyiccid($iccid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_item_group('$iccid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_item_group;
		}
		else
			return NULL;
	}
	
	function check_allowscanin($userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_allow_to_scan_in('$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_allow_to_scan_in;
		}
		else
			return NULL;
	}
	
	function check_valid_item_warranty($iccid,$userid,$channelid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_item_waranty_isvalid('$iccid','$userid','$channelid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_item_waranty_isvalid;
		}
		else
			return NULL;
	}
	
	function check_valid_item_konsinyasi($iccid,$userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_check_valid_item_consignment('$iccid','$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_check_valid_item_consignment;
		}
		else
			return NULL;
	}
	
	function check_valid_item_error_code($code)
	{
		//$this->db->select("*");
		$sql = "SELECT f_check_valid_item_error_code('$code')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_check_valid_item_error_code;
		}
		else
			return NULL;
	}
	
	function get_setday($userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_settlement_day('$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_settlement_day;
		}
		else
			return NULL;
	}
	
	function get_cluster($userid)
	{
		$this->db->select("*");
		$this->db->from("f_get_cluster('".$userid."')");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_cluster;
		}
		else
			return NULL;
	}

	function get_closestgallery($userid,$lonmax,$lonmin,$latmax,$latmin)
	{
		$this->db->select("gallery_id,gallery_name,gallery_address");
		$this->db->from("t_mtr_gallery");
		$this->db->where("cast(lon as decimal) > ".$lonmin." and cast(lon as decimal) < ".$lonmax." and cast(lat as decimal) > ".$latmin." and cast(lat as decimal) < ".$latmax);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_bisnisfocusbyoutlet($outletid)
	{
		$this->db->select("outlet_business_focus");
		$this->db->from("t_mtr_outlet_data");
		$this->db->where("outlet_id",$outletid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_sellevent()
	{
		$this->db->select("sell_event_id, sell_event_name");
		$this->db->from("t_mtr_sell_event");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_keyword($userid)
	{
		$tanggalskr = date("Y-m-d");
		/*$this->db->select("keyword, valid_date");
		$this->db->from("t_keywords");
		$this->db->where("user_id = '".$userid."' and valid_date = '".$tanggalskr."'");*/
		$query = $this->db->query("select keyword, valid_date from t_keywords where user_id = '".$userid."' and valid_date = '".$tanggalskr."' limit 1");
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_keywordtoday($userid,$keyword)
	{
		$tanggalskr = date("Y-m-d");
		//$this->db->select("keyword,client");
		//$this->db->from("t_keywords");
		//$this->db->where("user_id = '".$userid."' and keyword='".$keyword."' and valid_date = '".$tanggalskr."'");
		//$query = $this->db->get();
		
		$query = $this->db->query("select keyword, client from t_keywords where user_id = '".$userid."' and keyword='".$keyword."' and valid_date = '".$tanggalskr."' limit 1");
		
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function update_keyword($userid,$keyword,$client)
    {
		$data = array(
			'valid_date' => date('Y-m-d'),
			'keyword' => $keyword,
			'client' => $client
		);
		$this->db->where("user_id = '".$userid."'");
		$this->db->update('t_keywords', $data);
    }

	function update_keyword1($userid,$client)
    {
		$data = array(
			'client' => $client
		);
		$this->db->where("user_id = '".$userid."'");
		$this->db->update('t_keywords', $data);
    }

	function add_keyword($userid,$keyword,$client)
    {
		$data = array(
			'user_id' => $userid,
			'valid_date' => date("Y-m-d"),
			'keyword' => $keyword,
			'client' => $client
		);
		$this->db->insert('t_keywords', $data);
    }

	function get_lastnews($territory_id)
	{
		$this->db->select("news_content");
		$this->db->from("t_ntf_news");
		$this->db->where("territory_id = $territory_id order by created_on desc limit 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_trackint()
	{
		$this->db->select("lookup_value");
		$this->db->from("t_mtr_lookup");
		$this->db->where("lookup_key = 'mobile_lonlat_interval' limit 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_min_password()
	{
		$this->db->select("lookup_value");
		$this->db->from("t_mtr_lookup");
		$this->db->where("lookup_key = 'account_min_length' limit 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->lookup_value;
		}
		else
			return NULL;
	}
	
	function get_max_attempt()
	{
		$this->db->select("lookup_value");
		$this->db->from("t_mtr_lookup");
		$this->db->where("lookup_key = 'account_max_attemp' limit 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->lookup_value;
		}
		else
			return NULL;
	}

	function get_reportto($userid)
	{
		$this->db->select("reporting_to");
		$this->db->from("t_mtr_user");
		$this->db->where("user_id",$userid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_parentid($userid)
	{
		$this->db->select("parent_id");
		$this->db->from("t_mtr_territory");
		$this->db->where("user_id = '".$userid."' limit 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_parentidbyter($territoryid)
	{
		$this->db->select("parent_id");
		$this->db->from("t_mtr_territory");
		$this->db->where("territory_id = ".$territoryid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function add_merchancapture($datakirim)
	{
		$userid = $datakirim['user_id'];
		$channelid = $datakirim['channel_id'];
		$linkpath = $datakirim['link'];
		$longitude = $datakirim['lon'];
		$latitude = $datakirim['lat'];

		if($datakirim['tanggal'] != NULL || $datakirim['tanggal'] != "")
			$data = array(
			'channel_id' => $channelid,
			'image' => $linkpath,
			'lon' => $longitude,
			'lat' => $latitude,
			'istatus' => 1,
			'created_by' => $userid,
			'updated_by' => $userid,
			'user_id' => $userid,
			'created_on' => $datakirim['tanggal'],
			'updated_on' => $datakirim['tanggal'],
			'keyword' => $datakirim['keyword'],
			'pic_type' => $datakirim['pic_type']
			);
		else
			$data = array(
			'channel_id' => $channelid,
			'image' => $linkpath,
			'lon' => $longitude,
			'lat' => $latitude,
			'istatus' => 1,
			'created_by' => $userid,
			'updated_by' => $userid,
			'user_id' => $userid,
			'keyword' => $datakirim['keyword'],
			'pic_type' => $datakirim['pic_type']
			);
		$this->db->insert('t_trx_merchan_capture', $data);
	}

	function get_vcombobusinessfocus()
	{
		$this->db->select("member_value, member_display");
		$this->db->from("v_combo_business_focus");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
		function get_vcomboagentsellingtype()
	{
		$this->db->select("member_value, member_display");
		$this->db->from("v_combo_agent_selling_type");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_vcomboclaimprogam()
	{
		$this->db->select("mvalue, mdisplay");
		$this->db->from("v_combo_card_claim_program");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}


	function get_vcomboagenttype()
	{
		$this->db->select("member_value, member_display");
		$this->db->from("v_combo_agent_type");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}

	function get_groupid($userid)
	{
		$this->db->select("user_group_id");
		$this->db->from("t_mtr_user");
		$this->db->where("user_id",$userid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function is_use_top($groupid)
	{
		$this->db->select("is_use_top");
		$this->db->from("t_mtr_user_group");
		$this->db->where("user_group_id",$groupid);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		    $row = $query->row();
		   return $row->is_use_top;
		}
		else
			return NULL;
	}
	
	function get_top()
	{
		$this->db->select("top_id, top_name");
		$this->db->from("t_mtr_top");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_term_konsinyasi()
	{
		$this->db->select("term_id, term_name");
		$this->db->from("t_mtr_consignment_term");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}


	
	function get_programho($program_type_id)
	{
		$this->db->select("program_id as mvalue, caption as mdisplay");
		$this->db->from("t_mtr_program");
		$this->db->where("territory_id = 1 and program_type_id = '".$program_type_id."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_programcluster($user_id,$program_type_id)
	{
		$this->db->select("program_id as mvalue, caption as mdisplay");
		$this->db->from("t_mtr_program");
		$this->db->where("territory_id = f_get_cluster('".$user_id."') and program_type_id = '".$program_type_id."' and istatus = 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_appupdate($user_id,$versionterinstall,$jenishandset)
	{
		$this->db->select("app_version,app_path");
		$this->db->from("t_mtr_update_mobile_app");
		$this->db->where("update_id = f_get_update_app(f_get_cluster('".$user_id."'),'".$versionterinstall."','".$jenishandset."') LIMIT 1");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function getCreditLimit($userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_credit_limit('$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_credit_limit;
		}
		else
			return NULL;
	}
	
	function getCurrentLimit($userid)
	{
		//$this->db->select("*");
		$sql = "SELECT f_get_current_limit('$userid')";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_current_limit;
		}
		else
			return NULL;
	}
	
	function get_jatuhtempo_outlet($userid)
	{
		$sql = "select o.outlet_id as channel_id,o.outlet_name as channel_name,si.sell_in_id,si.sell_in_date from t_mtr_outlet o
      left outer join t_trx_sell_in si on o.outlet_id=si.channel_id
	  left outer join t_mtr_top mt on mt.top_id=si.top_id
	  where si.sell_in_date+mt.top_day='".date("Y-m-d")."' and mt.top_day>0 and si.user_id='$userid' and si.istatus IN(1,2)";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_jatuhtempo_agent($userid)
	{
		$sql = "select o.agent_id as channel_id,o.agent_name as channel_name,si.sell_in_id,si.sell_in_date from t_mtr_agent o
      left outer join t_trx_sell_in si on o.agent_id=si.channel_id
	  left outer join t_mtr_top mt on mt.top_id=si.top_id
	  where si.sell_in_date+mt.top_day='".date("Y-m-d")."' and mt.top_day>0 and si.user_id='$userid' and si.istatus IN(1,2)";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	function get_sell_in_detail($sellinid,$userid)
	{
		$sql = "select count(sid.*) as juml,sum(f_get_price(i.iccid,'$userid')) as tagihan from t_mtr_item i
		left outer join t_trx_sell_in_detail sid on i.iccid=sid.iccid where sid.sell_in_id='$sellinid'";
		
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	function get_sell_in_item($sellinid)
	{
		$sql = "select sid.iccid from t_trx_sell_in_detail sid where sid.sell_in_id='$sellinid'";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_konsinyasi($terrid)
	{
		$sql = "SELECT * FROM t_trx_consignment WHERE istatus = 1 AND territory_id = '$terrid' ORDER BY consignment_id ASC";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_item_konsinyasi($conid)
	{
		$sql = "SELECT iccid,price FROM t_trx_consignment_detail WHERE consignment_id = '$conid' ORDER BY iccid ASC";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function update_konsinyasi($conid,$istatus)
    {
		$data = array(
			'istatus' => $istatus
		);
		$this->db->where("consignment_id = '".$conid."'");
		return $this->db->update('t_trx_consignment', $data);
    }
	
	// Ini kita ganti dengan normalize_consignment()
	// function update_konsinyasi_detail($conid,$iccid,$istatus)
	// {
	// 	$data = array(
	// 		'istatus' => $istatus
	// 	);
	// 	$this->db->where("consignment_id = '".$conid."' and iccid = '".$iccid."'");
	// 	return $this->db->update('t_trx_consignment_detail', $data);
	// }
	
	public function normalize_consignment($consignment_id, $iccid)
	{
		$data = array(
				'istatus' => 1,
				'transaction_type' => 'Konsinyasi - Tidak terjual'
			); 

    $this->db->where('transaction_type', 'Konsinyasi');
    $this->db->where('transaction_id', $consignment_id);
    $this->db->where_not_in('iccid', $iccid);
		$this->db->update('t_trx_scan_in_detail', $data);

    $this->db->where('consignment_id', $consignment_id);
    $this->db->where_not_in('iccid', $iccid);
		$this->db->update("t_trx_consignment_detail", array("istatus" => 2) );
	}

	function get_msg($usrid)
	{
		$sql = "SELECT * FROM t_msg WHERE msg_to ='$usrid' AND msg_date = CURRENT_DATE AND istatus = 1 ORDER BY msg_id DESC";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_scan_in_items($usrid)
	{
		$sql = "SELECT sd.iccid FROM t_trx_scan_in_detail sd
		left outer join t_trx_scan_in si on si.scan_in_id=sd.scan_in_id
		where si.user_id='$usrid' and sd.istatus='1'";
		
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
	
	function get_territory_type($terid)
	{
		//$this->db->select("*");
		$sql = "SELECT territory_type_id
FROM t_mtr_territory ter where territory_id='$terid'
";
		//$this->db->from("f_check_valid_item('".$iccid."',"".$userid."")");
		//$this->db->from("f_check_valid_item('89620951012000432909','89348914')");
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->territory_type_id;
		}
		else
			return NULL;
	}
	
	function get_channel_id_by_territory_id($territory_id)
	{
		$sql = "SELECT f_get_channel_id_by_territory_id('$territory_id')";
		
		$query = $this->db->query($sql);
		
		//$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->f_get_channel_id_by_territory_id;
		}
		else
			return NULL;
	}
    
//Enhanched by RAA
function updateLoginStatus($user_id,$status)
    {
        $this->db->update('t_mtr_user', array('is_login'=>$status), array("user_id"=>$user_id));
    } 
    
    
//donny here
function getaccess($usergroupid){
$sql = "SELECT lookup,status FROM t_mtr_mobile_user_access WHERE user_group_id = '$usergroupid'";
		
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
    }   
    
    
    
    
    	function get_vcomboalasanjc()
	{
		$this->db->select("lookup_value, lookup_desc");
		$this->db->from("t_mtr_lookup");
        $this->db->where("lookup_key","alasan_jc");
        $this->db->order_by("lookup_value","asc");
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
		   return $query->result_array();
		}
		else
			return NULL;
	}
    	function add_merchandiser($datakirim)
	{
		$userid = $datakirim['user_id'];
		$channelid = $datakirim['channel_id'];
		$linkpath = $datakirim['link'];
		$longitude = $datakirim['lon'];
		$latitude = $datakirim['lat'];
        
        $territory=0;
        $territory=$this->db->query("select f_get_territory('$channelid')")->row()->f_get_territory;
        
   		if($datakirim['tanggal'] != NULL || $datakirim['tanggal'] != "")
			$data = array(
			'partner_id' => $channelid,
            //'territory_id' => "f_get_territory_id_by_channel_id('$channelid')",
			'territory_id' => $territory,
            'img_outlet_with_keyword' => $linkpath,
			'lon' => $longitude,
			'lat' => $latitude,
			'is_posm' => 0,
			'created_by' => $userid,
			'updated_by' => $userid,
			'user_id' => $userid,
			'merchandiser_date' => $datakirim['tanggal'],
			'keyword' => $datakirim['keyword']
			);
		else
			$data = array(
			'partner_id' => $channelid,
            'territory_id' => $territory,
			'img_outlet_with_keyword' => $linkpath,
			'lon' => $longitude,
			'lat' => $latitude,
			'is_posm' => 0,
			'created_by' => $userid,
			'updated_by' => $userid,
			'user_id' => $userid,
            'merchandiser_date' => date('Y-m-d',time()),
			'keyword' => $datakirim['keyword'].$datakirim['pic_type']
			);
   
 // echo var_dump($data);
  //exit(); 

		$this->db->insert('t_trx_merchandiser', $data);
        //exit();
        
        
	}
	
    function add_merchandiser_detail($datakirim)
	{
	   $mid=$datakirim['merchandiser_id'];
    		$data = array(
            'merchandiser_id'=>$mid,
			'img' => $datakirim["img"],
            'img_type'=>$datakirim["pic_type"],
			);
		
        $this->db->query("update t_trx_merchandiser set is_posm=1 where merchandiser_id='$mid'");
		$this->db->insert('t_trx_merchandiser_detail', $data);
	}
    function get_merchandiserheader($jpg){
        $merchandiserid=$this->db->query("select merchandiser_id from t_trx_merchandiser where img_outlet_with_keyword like '%$jpg.%'")->row()->merchandiser_id;
        return $merchandiserid;
    }
}


?>